<?php
namespace app\index\controller;
use think\Controller;
use think\Request;
use think\captcha\Captcha;
use think\facade\Session;
use think\facade\Config;
use think\Db;
use \think\facade\Lang;

class Ceshi extends controller
{
   public function index(){
   	$data=Db::Query("SELECT  * FROM lily_listkey WHERE rangde !='' and id>9196");
   	foreach ($data as $key => $value) {

   		$a=explode(',', $value['rangde']);
   		foreach ($a as $k => $v) {
   			if(strpos($v,'"')!==false){
              $unit='"';
   			}else{
   				if(strpos($v,'mm')!==false){
                        $unit='mm';
	   			}else{
	   				if(strpos($v,' ')!==false){
		              $unit=explode(' ',$v)[1];
		   			}else{
		   				$unit='';
		   			}
		   		}
   			}
   			if($unit=='greater'){
   				$unit='';
   			}
   			if(strpos($v,'greater')!==false){
              $min=$v+0;
              $max='';
   			}else{
   				$b=explode('~', $v);
   				$min=trim($b[0])+0;
   				$max=trim($b[1])+0;
   				$c=explode(' ', $b[1]);
                if(strpos($c[0],'k')!==false||strpos($c[0],'K')!==false){
                	$times=1000;
                	$times_unit='k';
                }else{
                	$times='';
                	$times_unit='';
                }
   			}
   			echo "insert into lily_listkey_range (`list_key_id`,`min`,`max`,`unit`,`times`,`times_unit`,`seq`) values (".$value['id'].",'".$min."','".$max."','".$unit."','".$times."','".$times_unit."',".$k.");</br>";
   			
   		}
   	}
   }

   public function index2(){
   	$data=Db::Query("SELECT  * FROM lily_listkey WHERE order_sort !='' AND nosearch=''");
   	foreach ($data as $key => $value) {

   		$a=explode(',', $value['order_sort']);
   		foreach ($a as $k => $v) {
   			
   			echo "insert into lily_listkey_reorder (`list_key_id`,`val`,`seq`) values (".$value['id'].",'".$v."',".$k.");</br>";
   			
   		}
   	}
   }

   public function index3(){
   	$data=Db::Query("SELECT  * FROM lily_right_table WHERE class_id in (SELECT  class_id FROM lily_right_table WHERE fileds_alias LIKE '%PN%' ) ");
   	foreach ($data as $key => $value) {
   		if($value['fileds']=='Gear'){
           echo "UPDATE lily_right_table SET fileds_alias='' where id=".$value['id'].";</br>";
      }else{
           echo "UPDATE lily_right_table SET fileds_alias='".htmlspecialchars($data[$key+1]['fileds_alias'])."' where id=".$value['id'].";</br>";
      }
   	}
   }

   public function index4(){
    $data=Db::Query("SELECT   url_product,id,COUNT(id) AS a from lily_products GROUP BY url_product HAVING a>1");
    foreach ($data as $key => $value) {
        $data1=Db::Query("SELECT  * from lily_products where url_product='".$value['url_product']."'");
        foreach ($data1 as $k => $v) {
            if($k!=0){
                echo "update lily_products set url_product='".$v['url_product']."~".$v['id']."' where id=".$v['id'].";</br>";
            }
        }
        
    }
   }

   public function index5(){
    $data=Db::Query("SELECT   * from lily_classify_canonical");
    foreach ($data as $key => $value) {
        $master=get_class_uplower_url($value['master_class_id']);
        $c=get_class_uplower_url($value['c_class_id']);
        echo $c.",".$master."</br>";
        
    }
   }


   public function index6(){
    $str="39,40,41,42,43,44,45,49,50,51,52,53,54,93,94,95,56,57,58,63,64,65,66,67,68,69,70,71,75,76,77,79,80,81,82,83,84,1161,88,89,90,91,120,121,122,123,124,125,135,136,137,127,128,129,130,131,132,212,213,214,138,139,140,141";
    $a=explode(',',$str);
    foreach ($a as $key => $value) {
        //echo $key."<br>";
        echo "SELECT pid,max(case when `key` = 'System of Measurement' then `value` else null end) as `System_of_Measurement` from lily_products_detail WHERE class_id=".$value." group by pid union all ";
    }
   }


   public function index7(){
    $str="39,40,41,42,43,44,45,49,50,51,52,53,54,93,94,95,56,57,58,63,64,65,66,67,68,69,70,71,75,76,77,79,80,81,82,83,84,1161,88,89,90,91,120,121,122,123,124,125,135,136,137,127,128,129,130,131,132,212,213,214,138,139,140,141";
    $a=explode(',',$str);
    $s="select * from (";
    foreach ($a as $key => $value) {
        
       $s.="select ".$value." UNION all ";
        
    }
    $s=$s.") as tmp,SELECT pid,max(case when `key` = 'System of Measurement' then `value` else null end) as `System_of_Measurement` from lily_products_detail as t WHERE t.class_id=tmp.cid group by pid  ";
    echo $s;
   }


   public function index8(){
    $detail=Db::table('lily_products_detail')->where([['id','>',10000],['id','<=',500000]])->select();
    
    foreach ($detail as $key => $value) {
        
        echo json_encode($value);

        
        
    }
    
   }

    public function index9(){
        $data=Db::Query("SELECT  url_product,id FROM lily_products WHERE class_id IN (SELECT c_class_id as class_id FROM lily_classify_canonical GROUP BY c_class_id ) AND url_product not LIKE '%\_%'");
        foreach ($data as $key => $value) {
            
            echo "update lily_products set url_product='".$value['url_product']."_".$value['id']."' where id=".$value['id'].';</br>';

       }
    }

    public function index10(){
        $data=Db::Query("SELECT part_number,id from lily_products WHERE class_id IN (SELECT c_class_id FROM lily_classify_canonical GROUP BY c_class_id) and id>80000");
        
        foreach ($data as $key => $value) {
            echo "update lily_products set url_product='".change_lower_symbol($value['part_number'])."_".$value['id']."' where id=".$value['id'].';</br>';
            

       }
       
    }


     public function index11(){
        $str="343,1592,350,1582,416,1593,342,1581,351,1583,344,1605,345,1606,346,1607,347,1608,441,451,443,455,398,1587,91,288,335,1308,1538,348,1309,1539,212,682,701,1278,220,227,79,228,415,1584,409,1310,1540,352,1311,1541,354,1585,316,1591,315,1580,396,1586,83,353,1570,286,730,214,684,703,1280,218,43,222,219,57,622,404,1600,399,1595,400,1596,401,1597,402,1598,403,1599,312,1604,340,1566,135,464,531,658,136,465,532,659,137,466,533,660,140,459,687,718,765,213,683,702,1279,56,1302,700,762,699,761,698,760,697,729,695,726,693,724,714,752,711,748,708,745,715,753,712,749,709,746,696,728,694,725,692,723,713,754,710,750,707,747,132,679,648,759,1480,131,678,647,758,1479,130,677,644,757,1477,129,676,643,756,1476,128,675,640,755,1474,127,674,639,751,1473,1162,1158,45,88,44,75,40,42,76,349,1568,331,1304,1534,332,1305,1535,53,1300,50,1299,49,1296,334,1307,1537,311,1565,395,1571,333,1306,1536,51,1297,414,1569,310,1303,1533,338,1554,413,1557,412,1556,339,1555,314,1553,54,1301,397,1572,509,632,121,516,650,120,515,649,123,518,654,122,517,653,125,520,656,124,519,655,540,664,541,665,52,1298,58,539,663,534,666,77,536,668,535,667,141,460,688,719,766,138,457,685,716,763,405,1609,406,1610,39,41,538,662,537,661,94,1314,95,1315,93,1313,336,1546,313,1545,337,1547,341,1567,407,1611,408,1612,410,1548,411,1549,139,458,686,717,764";
        $data=explode(',',$str);
        foreach ($data as $key => $value) {
            $c_class_id=Db::table('lily_classify_canonical')->where([['c_class_id','=',$value],['status','=',1]])->find();
            if(!empty($c_class_id)){
                 echo get_class_uplower_url($value).",".get_class_uplower_url($c_class_id['master_class_id'])."</br>";
            }
           /* else{
                $master_class_id=Db::table('lily_classify_canonical')->where([['master_class_id','=',$value],['status','=',1]])->find();
                if(empty($master_class_id)){
                    echo get_class_uplower_url($value)."</br>";
                }
            }*/

        }
         
    }

    /**/
    public function del(){
        $sql="SELECT id FROM lily_products where class_id=1722";
        $a=Db::Query($sql);
        foreach ($a as $key => $value) {
           echo "delete from lily_products_detail where pid=".$value['id'].";</br>";
        }
        echo "delete from lily_products where class_id=1722;";
    }

    public function ceshi_pdf(){
        $sql="SELECT url_product,pdf FROM lily_products where pdf IS NOT null and pdf!=''";
        $data1=Db::query($sql);
        foreach ($data1 as $key => $value) {
            echo $value['pdf']."--".$value['url_product'].";</br>";
        }
    }


    public function ceshi_pdf_1(){
        $url="https://image.lily-bearing.com/pdf/o-rings/o432432423423rings.pdf";
        echo file_get_contents($url);
    }



    public function tag(){
        $min_class_id=array();
        $min_class_id=class_down(5,$min_class_id);
        dump($min_class_id);
    }


    public function jh(){
        $sql="SELECT id,cname FROM lily_classify WHERE id  IN (39,40,41,42,43,44,45,1157,1158,1159,1160,1162,1163,1164,49,50,51,52,53,54,93,94,95,56,57,58,63,64,65,66,67,68,69,70,71,73,74,79,80,81,82,83,84,1161,88,89,90,91,120,121,122,123,124,125,135,136,137,127,128,129,130,131,132,212,213,214,138,139,140,141)";
        $data=Db::Query($sql);
        foreach ($data as $key => $value) {
            $sql2="select * from lily_right_table where class_id=".$value['id'];
            $data2=Db::Query($sql2);
            if(!empty($data2)){
                $str='';
                foreach ($data2 as $k => $v) {
                    
                    $str.=$v['fileds'].',';
                    
                }
                echo $value['cname']."|https://www.lily-bearing.com/".get_class_uplower_url($value['id'])."|".rtrim($str,',').";</br>";
            }

        }
    }

    public function getminclass_id(){
        $class_id=2138;
        $min_class_id=array();
        $min_class_id=class_down_all($class_id,$min_class_id);/*获取到最小分类的id*/
                
        $fitle='';
      
        foreach ($min_class_id as $key => $value) {
            $fitle.=$value.',';         
        }
        echo trim($fitle,',');
    }

    public function getminclass_id_cross(){
        $class_id=1374;
        $min_class_id=array();
        $min_class_id=class_down_all($class_id,$min_class_id);/*获取到最小分类的id*/
                
        $fitle='';
      
        foreach ($min_class_id as $key => $value) {
            $fitle.=$value.',';         
        }
        $str=trim($fitle,',');

        $sql="SELECT part_number,id FROM  lily_products WHERE class_id IN (".$str.")";
        $a=Db::query($sql);
        foreach ($a as $key => $value) {
            $b=Db::table('lily_cross')->where('part_number',$value['part_number'])->find();
            if(empty($b)){
                echo "insert into lily_cross (`cb_id`,`pid`,`cross_number`,`part_number`) values (14,'".$value['id'].",','".$value['part_number']."','".$value['part_number']."');</br>";
            }
        }

    }


    public function imgt(){
        $image = \think\Image::open('https://image.lily-bearing.com/images/class/1660284935862.jpg');
        $image->thumb(150,150,\think\Image::THUMB_CENTER)->save('./thumb.png');
    }



    public function nav_title(){
        return $this->fetch();
    }

    public function adf(){
        $b=Db::table('lily_listkey_es4')->field('wname,id')->select();
        foreach ($b as $key => $value) {
           echo "update lily_listkey_es4 set alias='".lang($value['wname'])."' where id=".$value['id'].";</br>";
           
        }
    }


     public function detailceshi(){
        $b="SELECT value,id FROM lily_products_detail WHERE alias_value='' and (value is not null and value!='') LIMIT 0,100000";
        $b=Db::query($b);
        
        foreach ($b as $key => $value) {
           //echo 'update lily_products_detail set alias_key="'.lang($value['key']).'" where id='.$value['id'].';</br>';
           $sql="update lily_products_detail set alias_value='".lang($value['value'])."' where id=".$value['id'];
           Db::Query($sql);
        }
    }
    

    public function adf32432423(){
        $b=Db::table('lily_right_table1')->field('fileds,id')->select();
        foreach ($b as $key => $value) {
            if(!empty($value['fileds'])){
                echo 'update lily_right_table1 set fileds_alias="'.lang($value['fileds']).'" where id='.$value['id'].';</br>';
            }
           
        }
    }

    public function adf543543534(){
        $b=Db::table('lily_classify')->field('cname,id')->select();
        foreach ($b as $key => $value) {
            $sql="select * from lily_classifys where cname='".$value['cname']."'";
            $c=Db::Query($sql);
            if(!empty($c)){

                $sql1="select * from lily_classify_content where class_id=".$value['id'];
                $d=Db::Query($sql1);
                if(empty($c[0]['tab_title_des'])){
                    $tab_title_des='';
                }else{
                    $tab_title_des=lang($c[0]['tab_title_des']);
                }
                if(empty($d)){

                    $q="insert into lily_classify_content (`class_id`,`tab_title_des`,`total_des`,`filter_total_des`,`block_des`,`detail_des`) values (".$value['id'].",'".$tab_title_des."','".$c[0]['zh_des']."','".$c[0]['zh_des_s']."','".$c[0]['zh_jianjie']."','".$c[0]['zh_content']."')";
                }else{
                    $q="update lily_classify_content set tab_title_des='".$tab_title_des."',total_des='".$c[0]['zh_des']."',filter_total_des='".$c[0]['zh_des_s']."',block_des='".$c[0]['zh_jianjie']."',detail_des='".$c[0]['zh_content']."' where class_id=".$value['id'];
                }
                
                Db::Query($q);
               
            }
        }
    }

    public function adf543543534_seo(){
        $b=Db::table('lily_classify')->field('cname,id')->select();
        foreach ($b as $key => $value) {
            $sql="select * from lily_classifys where cname='".$value['cname']."'";
            $c=Db::Query($sql);
            if(!empty($c)){

                $sql1="select * from lily_classify_seo where class_id=".$value['id'];
                $d=Db::Query($sql1);
                
                if(empty($d)){

                    $q="insert into lily_classify_seo (`class_id`,`title`,`keywords`,`description`,`filter_title`,`filter_keywords`,`filter_description`) values (".$value['id'].",'".$c[0]['zh_title']."','".$c[0]['zh_keyword']."','".$c[0]['zh_description']."','".$c[0]['sel_zh_title']."','".$c[0]['sel_zh_keyword']."','".$c[0]['sel_zh_description']."')";
                }else{
                    $q="update lily_classify_seo set `title`='".$c[0]['zh_title']."',`keywords`='".$c[0]['zh_keyword']."',`description`='".$c[0]['zh_description']."',`filter_title`='".$c[0]['sel_zh_title']."',`filter_keywords`='".$c[0]['sel_zh_keyword']."',`filter_description`='".$c[0]['sel_zh_description']."' where class_id=".$value['id'];
                }
                
                Db::Query($q);
               
            }
        }
    }


    public function posturl(){
        $users=Db::table('lily_classify_seo')->field('title,keywords,description,id')->where([['id','>',760],['id','<>',926]])->select();
            foreach ($users as $key => $value) {
                
                $data=[];
                $url='https://dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation';
                $data['model']='qwen-14b-chat';

                $data['input']['prompt']='下面这段英文翻译成中文:'.$value['description'];
                $data  = json_encode($data);    
                $headerArray =array("Content-type:application/json",'Authorization:sk-640a5bc0cfba4d2b9f2111c06f924ce7');
                $curl = curl_init();
                curl_setopt($curl, CURLOPT_URL, $url);
                curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
                curl_setopt($curl, CURLOPT_SSL_VERIFYHOST,FALSE);
                curl_setopt($curl, CURLOPT_POST, 1);
                curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
                curl_setopt($curl,CURLOPT_HTTPHEADER,$headerArray);
                curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
                $output = curl_exec($curl);
                $output1 = json_decode($output,true);
                
                $output2= json_decode(str_replace('\n','</br>',$output), true);
                //dump($output1['output']['text']);exit;

                /*$sql='update skd_classify set `desc1`="'.strval(str_replace('"',"'",str_replace('</br>','\n',$output2['output']['text']))).'",`desc_yuan`="'.strval(str_replace('"',"'",$output1['output']['text'])).'" where id='.$value['id'];*/
                $sql='update lily_classify_seo set `description`="'.strval(str_replace('"',"'",$output1['output']['text'])).'" where id='.$value['id'];
                Db::query($sql);
            }
        
    }

    public function zhong_p(){
        $users=Db::table('b')->field('url_product')->select();
        foreach ($users as $key => $value) {
            $a=explode('_',$value['url_product']);
            $b=Db::table('lily_products')->field('url_product')->where('url_product',$a[0])->find();
            if(empty($b)){
                echo "update lily_products set url_product='".$a[0]."' where url_product='".$value['url_product']."';</br>";
            }
        }
    }

    public function group_part(){
        $sql="SELECT COUNT(id) AS a,part_number from lily_products GROUP BY part_number HAVING a>1";
        $data=Db::Query($sql);
        foreach ($data as $key => $value) {
           $sql2="SELECT id,url_product from lily_products where part_number='".$value['part_number']."'";
           $data2=Db::Query($sql2);
           $data3=array();
           foreach ($data2 as $k => $v) {
               $s=explode('_', $v['url_product']);
               if(count($s)<=1){
                $data3[]=$v['id'];
                if(count($data3)>1){
                    echo "update lily_products set url_product='".change_lower_symbol($value['part_number'])."_".$v['id']."' where id=".$v['id'].";</br>";
                    echo "insert into lily_redirect (`url`,`r_url`,`sm`) values ('/products/".$v['url_product']."/','/products/".change_lower_symbol($value['part_number'])."_".$v['id']."/','详情url不正确');</br>";
                }
               }
           }
        }
    }

}